﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SQLite;
using System.Globalization;
using System.Reactive.Linq;
using System.Threading.Tasks;
using SharpStockLib.Data;
using SharpStockLib.Fin;

namespace SharpStockLib.DataAccess.Providers
{
    /// <summary>
    /// Provider to SQLite data access.
    /// WARNING : any instance must disposed when not used in order to close existing connection(s).
    /// </summary>
    public class SQLiteProvider : IDataProvider, IDisposable
    {
        private readonly SQLiteConnection _connection;
        private readonly string _pathToDB;

        /// <summary>
        /// Default constructor.
        /// WARNING : make sure the instance is disposed (via .Dispose() or in a using() block) when not used anymore, or existing connection will not be closed.
        /// </summary>
        /// <param name="pathToDB">Path to the SQLite DB file.</param>
        public SQLiteProvider(string pathToDB)
        {
            // Creates the connection to the DB ; the FailIfMissing flag will raise an exception when trying to connect to a non-existing database (instead of just creating the file)
            _connection = new SQLiteConnection("Data Source=" + pathToDB + ";Version=3;FailIfMissing=True;Foreign Keys=True;");
            _connection.Open();

            if (_connection.State != ConnectionState.Open)
                throw new SQLiteException("No connection could be made using database : " + pathToDB);

            _pathToDB = pathToDB;
        }

        #region IDataProvider Members

        /// <summary>
        /// Gets the historical data for the instrument given in input from an SQLite database. The connection to the SQLite database must have been made by the constructor.
        /// If no start date and end date are supplied, all quotes will be returned.
        /// Throws an SQLiteException if the database is not connected.
        /// </summary>
        /// <exception cref="SQLiteException">If the database is not connected.</exception>
        /// <param name="symbol">The name of the stock whose data must be retrieved</param>
        /// <param name="startDate">The date from which the data must be retrieved</param>
        /// <param name="endDate">The date until which the data must be retrieved</param>
        /// <returns>A list of data as an IEnumerable of IData. If no data was found, returns null.</returns>
        public IEnumerable<IData> GetHistoricalData(string symbol, DateTime? startDate, DateTime? endDate)
        {
            if (_connection != null && _connection.State == ConnectionState.Open)
            {
                string tableName = symbol.StartsWith("/") ? "eodFut" : "eod";

                // Gets the data from the DB
                SQLiteCommand cmd = _connection.CreateCommand();
                cmd.CommandText = "select * from " + tableName + " where symbol = ?";
                SQLiteParameter param = cmd.CreateParameter();
                cmd.Parameters.Add(param);
                param.Value = symbol.ToUpper();

                // Dates
                if (startDate != null)
                {
                    cmd.CommandText += " and date >= ?";
                    SQLiteParameter paramStartDate = cmd.CreateParameter();
                    cmd.Parameters.Add(paramStartDate);
                    paramStartDate.Value = ((DateTime)startDate).ToString("yyyy-MM-dd");
                }
                if (endDate != null)
                {
                    cmd.CommandText += " and date <= ?";
                    SQLiteParameter paramEndDate = cmd.CreateParameter();
                    cmd.Parameters.Add(paramEndDate);
                    paramEndDate.Value = ((DateTime)endDate).ToString("yyyy-MM-dd");
                }

                cmd.CommandText += " order by date ASC";

                // Fetches the data to a List.
                SQLiteDataReader reader = cmd.ExecuteReader();
                if (reader != null && reader.HasRows)
                {
                    List<IData> resultList = new List<IData>();

                    while (reader.Read())
                    {
                        resultList.Add(new SimpleData(reader.GetDouble(2), reader.GetDouble(3), reader.GetDouble(4), reader.GetDouble(5), reader.GetInt32(6),
                            DateTime.ParseExact(reader.GetString(1), "yyyy-MM-dd", CultureInfo.InvariantCulture)));
                    }

                    // Cleans up the resources and returns
                    reader.Close();
                    reader.Dispose();

                    return resultList;
                }

                // No data or reader null : returns null.
                return null;
            }

            // This code is only reached if no connection could be made
            throw new SQLiteException("Not connected to database");
        }

        /// <summary>
        /// Reads all quotes for the given symbols from the database. A tad overkill, but useful for parallel programming tests.
        /// </summary>
        /// <param name="symbols">The list of symbols to get the quotes for.</param>
        /// <returns>A Dictionary containing the quotes. Key = symbol and Value = list of quotes as IData objects.</returns>
        public Dictionary<string, List<IData>> FullLoad(List<string> symbols)
        {
            if (_connection != null && _connection.State == ConnectionState.Open)
            {
                // Gets the data from the DB
                SQLiteCommand cmd = _connection.CreateCommand();

                bool mustGetFuts = false;
                Dictionary<string, List<IData>> result = new Dictionary<string, List<IData>>(symbols.Count);
                foreach (string symbol in symbols)
                {
                    result[symbol] = new List<IData>();
                    if (symbol.StartsWith("/"))
                        mustGetFuts = true;
                }

                // Any ordering command should be made on the client-side using LINQ's OrderBy. MUCH MUCH FASTER.
                cmd.CommandText = "select * from eod";
                if (mustGetFuts)
                    cmd.CommandText += " union select * from eodFut";

                // Fetches the data to a List.
                SQLiteDataReader reader = cmd.ExecuteReader();
                if (reader != null && reader.HasRows)
                {
                    while (reader.Read())
                    {
                        result[reader.GetString(0)].Add(new SimpleData(reader.GetDouble(2), reader.GetDouble(3), reader.GetDouble(4), reader.GetDouble(5), reader.GetInt32(6),
                            //    DateTime.ParseExact(reader.GetString(1), "yyyy-MM-dd", CultureInfo.InvariantCulture)));
                              ParseDate(reader.GetString(1))));

                    }

                    // Cleans up the resources
                    reader.Close();
                    reader.Dispose();

                    return result;
                }

                return null;
            }

            throw new SQLiteException("Not connected to database");
        }

        /// <summary>
        /// Reads all quotes for the given symbols from the database. A tad overkill, but useful for parallel programming tests. Shoud probably not be used in production.
        /// Uses the TPL to split the number of queries according to the number of CPUs. Probably not a good idea but an interesting thing to test nonetheless.
        /// </summary>
        /// <param name="symbols">The list of symbols to get the quotes for.</param>
        /// <returns>A Dictionary containing the quotes. Key = symbol and Value = list of quotes as IData objects.</returns>
        public Dictionary<string, List<IData>> FullLoadParallel(List<string> symbols)
        {
            // TODO: adapt code to load futures data ? Depends on how we revamp the design of the DB
            long count = Count("eod");
            long partSize = (long)(Math.Ceiling(Convert.ToDecimal(count) / Environment.ProcessorCount));

            // Here we build a temporary output buffer. This is where each thread will dump its results.
            List<Dictionary<string, List<SimpleData>>> output = new List<Dictionary<string, List<SimpleData>>>(Environment.ProcessorCount);
            for (int i = 0; i < Environment.ProcessorCount; i++)
                output.Add(new Dictionary<string, List<SimpleData>>());

            Parallel.For(0, Environment.ProcessorCount, i =>
            {
                long offset = i * partSize;

                using (SQLiteConnection connec = new SQLiteConnection(new SQLiteConnection("Data Source=" + _pathToDB + ";Version=3;")))
                {
                    connec.Open();

                    SQLiteCommand cmd2 = connec.CreateCommand();
                    cmd2.CommandText = "select * from eod limit " + partSize + " offset " + offset;
                    Console.WriteLine("SQL : " + cmd2.CommandText);

                    SQLiteDataReader reader = cmd2.ExecuteReader();
                    while (reader.Read())
                    {
                        string symbol = reader.GetString(0);
                        if(!output[i].ContainsKey(symbol))
                            output[i][symbol] = new List<SimpleData>();

                        output[i][symbol].Add(new SimpleData(reader.GetDouble(2),
                                                reader.GetDouble(3),
                                                reader.GetDouble(4),
                                                reader.GetDouble(5),
                                                reader.GetInt32(6),
                                                ParseDate(reader.GetString(1))));
                    }

                    reader.Close();
                    reader.Dispose();
                }
            });

            // We should now have all data : puts them all into the actual result structure.
            Dictionary<string, List<IData>> result = new Dictionary<string, List<IData>>(symbols.Count);
            foreach (Dictionary<string, List<SimpleData>> threadOutput in output)
            {
                foreach(string symbol in threadOutput.Keys)
                {
                    if(!result.ContainsKey(symbol))
                        result[symbol] = new List<IData>();

                    result[symbol].AddRange(threadOutput[symbol]);
                }
            }

            return result;
        }

        /* Pipeline pattern attempt. Didn't work as expected but I keep the code here for a good example.
        public void FullLoadPipeline(BlockingCollection<Dictionary<string, IData>> output)
        {
            try
            {
                if (connection != null && connection.State == ConnectionState.Open)
                {
                    // Gets the data from the DB
                    SQLiteCommand cmd = connection.CreateCommand();

                    // Any ordering command should be made on the client-side using LINQ's OrderBy. MUCH MUCH FASTER.
                    cmd.CommandText = "select * from eod order by date";

                    // Fetches the data to a List.
                    SQLiteDataReader reader = cmd.ExecuteReader();
                    if (reader != null && reader.HasRows)
                    {
                        DateTime lastDate = DateTime.Now;
                        Dictionary<string, IData> block = null;
                        while (reader.Read())
                        {
                            DateTime curDate = ParseDate(reader.GetString(1));
                            if (curDate != lastDate)
                            {
                                if (block != null)
                                    output.Add(block);

                                block = new Dictionary<string, IData>();
                            }
                            block[reader.GetString(0)] = new SimpleData(reader.GetDouble(2), reader.GetDouble(3), reader.GetDouble(4), reader.GetDouble(5), reader.GetInt32(6), curDate);
                        }

                        // Cleans up the resources
                        reader.Close();
                        reader.Dispose();
                    }
                }
                else
                    throw new SQLiteException("Not connected to database");
            }
            finally
            {
                output.CompleteAdding();
            }
        }*/

        /// <summary>
        /// Gets all stock tickers.
        /// </summary>
        /// <returns>A list of all stocks tickers.</returns>
        public List<string> ListStocks()
        {
            return ListSymbols("stocks");
        }

        /// <summary>
        /// Gets all futures tickers.
        /// </summary>
        /// <returns>A list of all futures tickers.</returns>
        public List<string> ListFutures()
        {
            return ListSymbols("futures");
        }

        /// <summary>
        /// Returns all the tickers from the given table.
        /// </summary>
        /// <exception cref="SQLiteException">If the database is not connected.</exception>
        /// <param name="tableName">The name of the table to get the tickers from.</param>
        /// <returns>A list of tickers.</returns>
        private List<string> ListSymbols(string tableName)
        {
            if (_connection != null && _connection.State == ConnectionState.Open)
            {
                SQLiteCommand cmd = _connection.CreateCommand();

                cmd.CommandText = "select symbol from " + tableName;

                // Fetches the data to a List.
                SQLiteDataReader reader = cmd.ExecuteReader();
                if (reader != null && reader.HasRows)
                {
                    List<string> result = new List<string>();
                    while (reader.Read())
                        result.Add(reader.GetString(0));

                    // Cleans up the resources
                    reader.Close();
                    reader.Dispose();

                    return result;
                }

                return null;
            }

            throw new SQLiteException("Not connected to database");
        }

        /// <summary>
        /// Counts the number of lines in the given table.
        /// </summary>
        /// <exception cref="SQLiteException">If the database is not connected.</exception>
        /// <param name="tableName">The name of the table to count the lines in.</param>
        /// <returns>The number of lines in the given table.</returns>
        private long Count(string tableName)
        {
            if (_connection != null && _connection.State == ConnectionState.Open)
            {
                SQLiteCommand cmd = _connection.CreateCommand();

                cmd.CommandText = "select count(*) from " + tableName;

                // Fetches the data to a List.
                long count = (long)cmd.ExecuteScalar();

                cmd.Dispose();

                return count;
            }

            throw new SQLiteException("Not connected to database");
        }

        /// <summary>
        /// Rx helper. Should get the data asynchronously but so far, it only transforms the synchronous results to an IObservable.
        /// TODO: do an actual asynchronous implementation (should be possible using yield returns)
        /// </summary>
        /// <param name="instrumentName">The name of the instrument whose data must be retrieved</param>
        /// <param name="startDate">The date from which the data must be retrieved</param>
        /// <param name="endDate">The date until which the data must be retrieved</param>
        /// <returns>An IObservable feed of IData matching the criteria that the consumer will be able to use asynchronously.</returns>
        public IObservable<IData> GetHistoricalDataAsync(string instrumentName, DateTime? startDate, DateTime? endDate)
        {
            return GetHistoricalData(instrumentName, startDate, endDate).ToObservable();
        }

        /// <summary>
        /// Gets all info about a stock from the database.
        /// </summary>
        /// <param name="symbol">The stock's symbol</param>
        /// <exception cref="SQLiteException">If the database is not connected.</exception>
        /// <returns>A Stock object containing all information about the stock or null if the symbol was not found.</returns>
        public Stock GetStockInfo(string symbol)
        {
            if (_connection != null && _connection.State == ConnectionState.Open)
            {
                using (SQLiteCommand cmd = _connection.CreateCommand())
                {
                    cmd.CommandText = "select * from stocks where symbol=?";
                    SQLiteParameter paramSymbol = cmd.CreateParameter();
                    cmd.Parameters.Add(paramSymbol);
                    paramSymbol.Value = symbol;

                    using (SQLiteDataReader reader = cmd.ExecuteReader())
                    {
                        if (reader.Read())
                        {
                            Stock result = new Stock(symbol);
                            result.Description = reader.GetString(1);
                            result.Sector = reader.GetString(2);

                            return result;
                        }
                    }
                }
            }

            return null;
        }

        #endregion

        #region IDisposable Members

        /// <summary>
        /// Closes and disposes the underlying SQLite connection.
        /// </summary>
        public void Dispose()
        {
            if (_connection != null && _connection.State == ConnectionState.Open)
            {
                _connection.Close();
                _connection.Dispose();
            }
        }

        #endregion

        /// <summary>
        /// Recoded ParseDate. I'm not proud of this one but, because of its simplicity, it beats the framework's DateTime.Parse() performance-wise.
        /// Of course, it performs less tests and can only read a certain pattern : yyyy-MM-dd or yyyy-MM-dd HH:mm:ss. Yeah, much simpler and much more stupid.
        /// This is not recommended for production code but it has a real impact when reading all the quotes from the DB.
        /// </summary>
        /// <param name="s">The string to parse for a date. Should be using one of the two following formats : "yyyy-MM-dd" or "yyyy-MM-dd HH:mm:ss"</param>
        /// <returns></returns>
        public static DateTime ParseDate(string s)
        {
            if (string.IsNullOrEmpty(s))
                throw new ArgumentNullException(s, "Input string cannot be null");

            int hyphen = s.IndexOf('-');
            string part1 = s.Substring(0, hyphen);
            int nextHyphen = s.IndexOf('-', hyphen + 1);
            string part2 = s.Substring(hyphen + 1, nextHyphen - hyphen - 1);
            string part3 = s.Substring(nextHyphen + 1);
            // Hours ?
            int hoursDelimiter = s.IndexOf(' ');
            if (hoursDelimiter > 0)
            {
                part3 = s.Substring(nextHyphen + 1, hoursDelimiter - nextHyphen - 1);
                int semicolon = s.IndexOf(':', hoursDelimiter);
                string hours = s.Substring(hoursDelimiter + 1, semicolon - hoursDelimiter - 1);
                int nextSemicolon = s.IndexOf(':', semicolon + 1);
                string min = s.Substring(semicolon + 1, nextSemicolon - semicolon - 1);
                string sec = s.Substring(nextSemicolon + 1);

                return new DateTime(IntParseFast(part1), IntParseFast(part2), IntParseFast(part3), IntParseFast(hours), IntParseFast(min), IntParseFast(sec));
            }

            return new DateTime(IntParseFast(part1), IntParseFast(part2), IntParseFast(part3));
        }

        /// <summary>
        /// Same as above, I coded my own version of Int.Parse for performance purpose, mostly to avoid culture parse and yadda yadda (see http://www.dotnetperls.com/int-parse-optimization for more info).
        /// This is stupid but fun and has an actual impact on overall performance.
        /// </summary>
        /// <param name="value">The string to parse for an int</param>
        /// <returns>The int </returns>
        public static int IntParseFast(string value)
        {
            int result = 0;
            for (int i = 0; i < value.Length; i++)
            {
                char letter = value[i];
                result = 10 * result + (letter - 48);
            }
            return result;
        }
    }
}
